﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;

namespace FYP14
{
    public class BAL
    {
        private static void ThrowException(string functionName, Exception ex)
        {
            throw new Exception("ORM:" + functionName + " [ " + ex.Message + " ]");
        }

        #region Doctor
        public static bool SaveDoctor(Doctor oDoctor)
        {
            try
            {
                Database DB = new Database();
                DB.OpenConnection();
                DB.ExecuteQuery("insert into doctor(Name, Specialization) Values ('" + oDoctor.Name + "','" + oDoctor.Specialization + "')");
                //Int32 PurchaseID = Convert.ToInt32(DB.GetDataTable("select Max(ID) as ID from tblPurchase").Rows[0]["ID"]);
                DB.CloseConnection();
                //return PurchaseID;
                return true;
            }
            catch (Exception ex)
            {
                ThrowException("SaveDoctor", ex);
                return false;
            }
        }

        public static DataTable GetAllDoctors()
        {
            try
            {
                Database DB = new Database();
                DB.OpenConnection();
                DataTable dt = DB.GetDataTable("select * from doctor");
                DB.CloseConnection();
                return dt;
            }
            catch (Exception ex)
            {
                ThrowException("GetAllDoctors", ex);
                return null;
            }
        }

        public static DataTable GetDoctor(Int32 ID)
        {
            try
            {
                Database DB = new Database();
                DB.OpenConnection();
                DataTable dt = DB.GetDataTable("SELECT * FROM doctor where doctor_id= " + ID);
                DB.CloseConnection();
                return dt;
            }
            catch (Exception ex)
            {
                ThrowException("GetDoctor", ex);
                return null;
            }
        }

        public static Int32 UpdateDoctor(Doctor oDoctor)
        {
            try
            {
                Database DB = new Database();
                DB.OpenConnection();
                DB.ExecuteQuery("update doctor set Name='" + oDoctor.Name + "', Specialization='" + oDoctor.Specialization + "' where doctor_id= " + oDoctor.ID);
                //Int32 PurchaseID = Convert.ToInt32(DB.GetDataTable("select Max(ID) as ID from tblOrder").Rows[0]["ID"]);
                DB.CloseConnection();
                return 0;
            }
            catch (Exception ex)
            {
                ThrowException("SavePurchase", ex);
                return 0;
            }
        }
        #endregion

        #region Patient
        public static bool SavePatient(Patient Patient)
        {
            try
            {
                Database DB = new Database();
                DB.OpenConnection();
                DB.ExecuteQuery("insert into Patient(Name, dob, record_no, nic, gender, age, contact_number, address) Values ('" + Patient.Name + "','" + Patient.dob + "','" + Patient.record_no + "','" + Patient.nic + "','" + Patient.gender + "','" + Patient.age + "','" + Patient.contact_number + "','" + Patient.address + "')");
                //Int32 PurchaseID = Convert.ToInt32(DB.GetDataTable("select Max(ID) as ID from tblPurchase").Rows[0]["ID"]);
                DB.CloseConnection();
                //return PurchaseID;
                return true;
            }
            catch (Exception ex)
            {
                ThrowException("SaveDoctor", ex);
                return false;
            }
        }

        public static DataTable GetAllPatients()
        {
            try
            {
                Database DB = new Database();
                DB.OpenConnection();
                DataTable dt = DB.GetDataTable("select * from Patient");
                DB.CloseConnection();
                return dt;
            }
            catch (Exception ex)
            {
                ThrowException("GetAllPatients", ex);
                return null;
            }
        }

        public static DataTable GetPatient(Int32 ID)
        {
            try
            {
                Database DB = new Database();
                DB.OpenConnection();
                DataTable dt = DB.GetDataTable("SELECT * FROM Patient where Patient_id= " + ID);
                DB.CloseConnection();
                return dt;
            }
            catch (Exception ex)
            {
                ThrowException("GetPatient", ex);
                return null;
            }
        }

        public static Int32 UpdatePatient(Patient oPatient)
        {
            try
            {
                Database DB = new Database();
                DB.OpenConnection();
                DB.ExecuteQuery("update Patient set Name='" + oPatient.Name + "', dob='" + oPatient.dob + "', record_no='" + oPatient.record_no + "', nic='" + oPatient.nic + "', gender='" + oPatient.gender + "', age='" + oPatient.age + "', contact_number='" + oPatient.contact_number + "', address='" + oPatient.address + "' where patient_id= " + oPatient.ID);
                //Int32 PurchaseID = Convert.ToInt32(DB.GetDataTable("select Max(ID) as ID from tblOrder").Rows[0]["ID"]);
                DB.CloseConnection();
                return 0;
            }
            catch (Exception ex)
            {
                ThrowException("UpdatePatient", ex);
                return 0;
            }
        }
        #endregion

        #region Nurse
        public static bool SaveNurse(Nurse oNurse)
        {
            try
            {
                Database DB = new Database();
                DB.OpenConnection();
                DB.ExecuteQuery("insert into Nurse(Name, Specialization) Values ('" + oNurse.Name + "','" + oNurse.Specialization + "')");
                //Int32 PurchaseID = Convert.ToInt32(DB.GetDataTable("select Max(ID) as ID from tblPurchase").Rows[0]["ID"]);
                DB.CloseConnection();
                //return PurchaseID;
                return true;
            }
            catch (Exception ex)
            {
                ThrowException("SaveNurse", ex);
                return false;
            }
        }

        public static DataTable GetAllNurses()
        {
            try
            {
                Database DB = new Database();
                DB.OpenConnection();
                DataTable dt = DB.GetDataTable("select * from Nurse");
                DB.CloseConnection();
                return dt;
            }
            catch (Exception ex)
            {
                ThrowException("GetAllNurses", ex);
                return null;
            }
        }

        public static DataTable GetNurse(Int32 ID)
        {
            try
            {
                Database DB = new Database();
                DB.OpenConnection();
                DataTable dt = DB.GetDataTable("SELECT * FROM Nurse where Nurse_id= " + ID);
                DB.CloseConnection();
                return dt;
            }
            catch (Exception ex)
            {
                ThrowException("GetNurse", ex);
                return null;
            }
        }

        public static Int32 UpdateNurse(Nurse oNurse)
        {
            try
            {
                Database DB = new Database();
                DB.OpenConnection();
                DB.ExecuteQuery("update Nurse set Name='" + oNurse.Name + "', Specialization='" + oNurse.Specialization + "' where Nurse_id= " + oNurse.ID);
                //Int32 PurchaseID = Convert.ToInt32(DB.GetDataTable("select Max(ID) as ID from tblOrder").Rows[0]["ID"]);
                DB.CloseConnection();
                return 0;
            }
            catch (Exception ex)
            {
                ThrowException("UpdateNurse", ex);
                return 0;
            }
        }
        #endregion

        #region Users
        public static bool SaveUser(User oUser)
        {
            try
            {
                Database DB = new Database();
                DB.OpenConnection();
                DB.ExecuteQuery("insert into application_user(login_id, password, user_role_id) Values ('" + oUser.login_id + "','" + oUser.password + "','" + oUser.user_role_id + "')");
                DB.CloseConnection();
                return true;
            }
            catch (Exception ex)
            {
                ThrowException("SaveUser", ex);
                return false;
            }
        }

        public static DataTable GetAllUsers()
        {
            try
            {
                Database DB = new Database();
                DB.OpenConnection();
                DataTable dt = DB.GetDataTable("select * from application_user");
                DB.CloseConnection();
                return dt;
            }
            catch (Exception ex)
            {
                ThrowException("GetAllUsers", ex);
                return null;
            }
        }

        public static DataTable GetUser(Int32 ID)
        {
            try
            {
                Database DB = new Database();
                DB.OpenConnection();
                DataTable dt = DB.GetDataTable("SELECT * FROM application_user where application_user_id= " + ID);
                DB.CloseConnection();
                return dt;
            }
            catch (Exception ex)
            {
                ThrowException("GetUser", ex);
                return null;
            }
        }

        public static Int32 UpdateUser(User oUser)
        {
            try
            {
                Database DB = new Database();
                DB.OpenConnection();
                DB.ExecuteQuery("update application_user set login_id = '" + oUser.login_id + "', password='" + oUser.password + "', user_role_id='" + oUser.user_role_id + "' where application_user_id= '" + oUser.application_user_id + "'");
                DB.CloseConnection();
                return 0;
            }
            catch (Exception ex)
            {
                ThrowException("UpdateUser", ex);
                return 0;
            }
        }
        #endregion

        #region Medicine
        public static bool SaveMedicine(Medicine oMedicine)
        {
            try
            {
                Database DB = new Database();
                DB.OpenConnection();
                DB.ExecuteQuery("insert into medicine(medicine_type, medicine_name, generic_name, chemical_name, potency, dosage_form) Values ('" + oMedicine.Medicine_Type + "','" + oMedicine.Medicine_Name + "','" + oMedicine.Generic_Name + "','" + oMedicine.Chemical_Name + "','" + oMedicine.Potency + "','" + oMedicine.Dosage_Form + "')");
                DB.CloseConnection();
                return true;
            }
            catch (Exception ex)
            {
                ThrowException("SaveMedicine", ex);
                return false;
            }
        }

        public static DataTable GetAllMedicines()
        {
            try
            {
                Database DB = new Database();
                DB.OpenConnection();
                DataTable dt = DB.GetDataTable("select * from medicine");
                DB.CloseConnection();
                return dt;
            }
            catch (Exception ex)
            {
                ThrowException("GetAllMedicines", ex);
                return null;
            }
        }

        public static DataTable GetMedicine(Int32 ID)
        {
            try
            {
                Database DB = new Database();
                DB.OpenConnection();
                DataTable dt = DB.GetDataTable("SELECT * FROM medicine where medicine_id= " + ID);
                DB.CloseConnection();
                return dt;
            }
            catch (Exception ex)
            {
                ThrowException("GetMedicine", ex);
                return null;
            }
        }

        public static Int32 UpdateMedicine(Medicine oMedicine)
        {
            try
            {
                Database DB = new Database();
                DB.OpenConnection();
                DB.ExecuteQuery("update medicine set medicine_type ='" + oMedicine.Medicine_Type + "', medicine_name ='" + oMedicine.Medicine_Name + "', generic_name ='" + oMedicine.Generic_Name + "', chemical_name ='" + oMedicine.Chemical_Name + "', potency ='" + oMedicine.Potency + "', dosage_form ='" + oMedicine.Dosage_Form + "' where medicine_id = '" + oMedicine.Medicine_id + "'");
                DB.CloseConnection();
                return 0;
            }
            catch (Exception ex)
            {
                ThrowException("UpdateMedicine", ex);
                return 0;
            }
        }
        #endregion

        #region Labtest
        public static DataTable GetAlllabtests()
        {
            try
            {
                Database DB = new Database();
                DB.OpenConnection();
                DataTable dt = DB.GetDataTable("select * from labtest");
                DB.CloseConnection();
                return dt;
            }
            catch (Exception ex)
            {
                ThrowException("GetAlllabtests", ex);
                return null;
            }
        }
        #endregion

        #region Schedule
        public static bool SaveSchedule(Schedule oSchedule)
        {
            try
            {
                Database DB = new Database();
                DB.OpenConnection();
                DB.ExecuteQuery("insert into schedule(doctor_id, Schedule_name) Values ('" + oSchedule.Doctor_id + "','" + oSchedule.Schedule_Name + "')");
                DB.CloseConnection();
                return true;
            }
            catch (Exception ex)
            {
                ThrowException("SaveSchedule", ex);
                return false;
            }
        }

        public static DataTable GetAllSchedules()
        {
            try
            {
                Database DB = new Database();
                DB.OpenConnection();
                DataTable dt = DB.GetDataTable("select * from schedule inner join doctor on schedule.doctor_id=doctor.doctor_id");
                DB.CloseConnection();
                return dt;
            }
            catch (Exception ex)
            {
                ThrowException("GetAllMedicines", ex);
                return null;
            }
        }

        public static DataTable GetSchedule(Int32 ID)
        {
            try
            {
                Database DB = new Database();
                DB.OpenConnection();
                DataTable dt = DB.GetDataTable("SELECT * FROM schedule where schedule_id= " + ID);
                DB.CloseConnection();
                return dt;
            }
            catch (Exception ex)
            {
                ThrowException("GetMedicine", ex);
                return null;
            }
        }

        public static Int32 Updateschedule(Schedule oschedule)
        {
            try
            {
                Database DB = new Database();
                DB.OpenConnection();
                DB.ExecuteQuery("update schedule set doctor_id='" + oschedule.Doctor_id + "', schedule_name ='" + oschedule.Schedule_Name + "' Where schedule_id=" + oschedule.Schedule_id);
                DB.CloseConnection();
                return 0;
            }
            catch (Exception ex)
            {
                ThrowException("UpdateMedicine", ex);
                return 0;
            }
        }
        #endregion

        #region ScheduleDetails
        public static bool SaveScheduleDetails(ScheduleDetail oSchedule)
        {
            try
            {
                Database DB = new Database();
                DB.OpenConnection();
                DB.ExecuteQuery("insert into schedule_detail(schedule_id, day_of_week,from_time,to_time) Values ('" + oSchedule.Schedule_id + "','" + oSchedule.DayOfWeek + "','" + oSchedule.from_time + "','" + oSchedule.to_time + "')");
                DB.CloseConnection();
                return true;
            }
            catch (Exception ex)
            {
                ThrowException("SaveSchedule", ex);
                return false;
            }
        }

        public static DataTable GetScheduleDetailsFroScheduleID(Int32 ID)
        {
            try
            {
                Database DB = new Database();
                DB.OpenConnection();
                DataTable dt = DB.GetDataTable("SELECT schedule_detail_id, day_of_week, CONVERT(VARCHAR(10), from_time, 101) as from_time, CONVERT(VARCHAR(10), to_time, 101) as to_time, schedule.* FROM schedule_detail inner join schedule on schedule_detail.schedule_id=schedule.schedule_id where schedule_detail.schedule_id= " + ID);
                DB.CloseConnection();
                return dt;
            }
            catch (Exception ex)
            {
                ThrowException("GetMedicine", ex);
                return null;
            }
        }

        public static DataTable GetScheduleDetail(Int32 ID)
        {
            try
            {
                Database DB = new Database();
                DB.OpenConnection();
                DataTable dt = DB.GetDataTable("SELECT schedule_detail_id, schedule_id, day_of_week, CONVERT(VARCHAR(10), from_time, 101) as from_time, CONVERT(VARCHAR(10), to_time, 101) as to_time FROM schedule_detail where schedule_detail.schedule_detail_id= " + ID);
                DB.CloseConnection();
                return dt;
            }
            catch (Exception ex)
            {
                ThrowException("GetMedicine", ex);
                return null;
            }
        }

        public static Int32 UpdatescheduleDetail(ScheduleDetail oschedule)
        {
            try
            {
                Database DB = new Database();
                DB.OpenConnection();
                DB.ExecuteQuery("update schedule_detail set day_of_week='" + oschedule.DayOfWeek + "', from_time ='" + oschedule.from_time + "', to_time ='" + oschedule.to_time + "' Where schedule_detail_id=" + oschedule.Schedule_Detail_id);
                DB.CloseConnection();
                return 0;
            }
            catch (Exception ex)
            {
                ThrowException("UpdateMedicine", ex);
                return 0;
            }
        }
        #endregion

        #region Appointment
        public static bool SaveAppointment(Appointment oAppointment)
        {
            try
            {
                Database DB = new Database();
                DB.OpenConnection();
                DB.ExecuteQuery("insert into Appointment(patient_id, schedule_id, Appointment_type, Appointment_date) Values ('" + oAppointment.Patient_ID + "','" + oAppointment.Schedule_ID + "','" + oAppointment.appointment_type + "','" + oAppointment.appointment_date.ToString("yyyy-MM-dd") + "')");
                //Int32 PurchaseID = Convert.ToInt32(DB.GetDataTable("select Max(ID) as ID from tblPurchase").Rows[0]["ID"]);
                DB.CloseConnection();
                //return PurchaseID;
                return true;
            }
            catch (Exception ex)
            {
                ThrowException("SaveDoctor", ex);
                return false;
            }
        }

        public static DataTable GetAllAppointments()
        {
            try
            {
                Database DB = new Database();
                DB.OpenConnection();
                DataTable dt = DB.GetDataTable("select * from Appointment inner join patient on Appointment.patient_id = patient.patient_id inner join schedule on appointment.schedule_id = schedule.schedule_id");
                DB.CloseConnection();
                return dt;
            }
            catch (Exception ex)
            {
                ThrowException("GetAllAppointments", ex);
                return null;
            }
        }

        public static DataTable GetAppointment(Int32 ID)
        {
            try
            {
                Database DB = new Database();
                DB.OpenConnection();
                DataTable dt = DB.GetDataTable("SELECT * FROM Appointment where Appointment_id= " + ID);
                DB.CloseConnection();
                return dt;
            }
            catch (Exception ex)
            {
                ThrowException("GetAppointment", ex);
                return null;
            }
        }

        public static Int32 UpdateAppointment(Appointment oAppointment)
        {
            try
            {
                Database DB = new Database();
                DB.OpenConnection();
                DB.ExecuteQuery("update Appointment set patient_id='" + oAppointment.Patient_ID + "', schedule_id='" + oAppointment.Schedule_ID + "', Appointment_type='" + oAppointment.appointment_type + "', Appointment_date='" + oAppointment.appointment_date + "' where Appointment_id= " + oAppointment.Appointment_ID);
                //Int32 PurchaseID = Convert.ToInt32(DB.GetDataTable("select Max(ID) as ID from tblOrder").Rows[0]["ID"]);
                DB.CloseConnection();
                return 0;
            }
            catch (Exception ex)
            {
                ThrowException("UpdateAppointment", ex);
                return 0;
            }
        }
        #endregion

        #region Visit
        public static Int32 SaveVisit(Visit oVisit)
        {
            try
            {
                Database DB = new Database();
                DB.OpenConnection();
                DB.ExecuteQuery("insert into Visit(appointment_id, visit_date) Values ('" + oVisit.Appointment_ID + "','" + oVisit.Visit_Date.ToString("yyyy-MM-dd") + "')");
                Int32 VisitID = Convert.ToInt32(DB.GetDataTable("select Max(Visit_ID) as ID from Visit").Rows[0]["ID"]);
                DB.CloseConnection();
                return VisitID;
            }
            catch (Exception ex)
            {
                ThrowException("SaveVisit", ex);
                return 0;
            }
        }

        public static DataTable GetAllVisits()
        {
            try
            {
                Database DB = new Database();
                DB.OpenConnection();
                DataTable dt = DB.GetDataTable("select * from Appointment inner join visit on Appointment.appointment_id = visit.appointment_id");
                DB.CloseConnection();
                return dt;
            }
            catch (Exception ex)
            {
                ThrowException("GetAllVisits", ex);
                return null;
            }
        }

        public static DataTable GetVisit(Int32 ID)
        {
            try
            {
                Database DB = new Database();
                DB.OpenConnection();
                DataTable dt = DB.GetDataTable("SELECT * FROM Visit where Visit_id= " + ID);
                DB.CloseConnection();
                return dt;
            }
            catch (Exception ex)
            {
                ThrowException("GetVisit", ex);
                return null;
            }
        }

        public static Int32 UpdateVisit(Visit oVisit)
        {
            try
            {
                Database DB = new Database();
                DB.OpenConnection();
                DB.ExecuteQuery("update visit set appointment_id='" + oVisit.Appointment_ID + "', visit_date='" + oVisit.Visit_Date + "' where visit_id= " + oVisit.Visit_ID);
                DB.CloseConnection();
                return 0;
            }
            catch (Exception ex)
            {
                ThrowException("UpdateVisit", ex);
                return 0;
            }
        }

        public static bool SaveVisitNote(VisitNote oVisitNote)
        {
            try
            {
                Database DB = new Database();
                DB.OpenConnection();
                DB.ExecuteQuery("insert into Visit_note(visit_id, blood_pressure, sugar, height, weight, comments) Values ('" + oVisitNote.Visit_ID + "','" + oVisitNote.BloodPresuusre + "','" + oVisitNote.Suger + "','" + oVisitNote.Height + "','" + oVisitNote.Weight + "','" + oVisitNote.Comments + "')");
                DB.CloseConnection();
                return true;
            }
            catch (Exception ex)
            {
                ThrowException("SaveVisit", ex);
                return false;
            }
        }

        public static bool SaveLabPrescription(LabPrescription oLabPrescription)
        {
            try
            {
                Database DB = new Database();
                DB.OpenConnection();
                DB.ExecuteQuery("insert into Lab_Prescription(visit_id, labtest_id) Values ('" + oLabPrescription.Visit_ID + "','" + oLabPrescription.LabTest_ID + "')");
                DB.CloseConnection();
                return true;
            }
            catch (Exception ex)
            {
                ThrowException("SaveVisit", ex);
                return false;
            }
        }

        public static bool SaveMedicinePrescription(MedicinePrescription oMedicinePrescription)
        {
            try
            {
                Database DB = new Database();
                DB.OpenConnection();
                DB.ExecuteQuery("insert into Medicine_Prescription(visit_id, Medicine_id) Values ('" + oMedicinePrescription.Visit_ID + "','" + oMedicinePrescription.Medicine_ID + "')");
                DB.CloseConnection();
                return true;
            }
            catch (Exception ex)
            {
                ThrowException("SaveVisit", ex);
                return false;
            }
        }
        #endregion

        #region Login
        public static DataTable GetLogin(string username, string password)
        {
            try
            {
                Database DB = new Database();
                DB.OpenConnection();
                DataTable dt = DB.GetDataTable("SELECT * FROM application_user where login_id='" + username + "' and password='" + password + "';");
                DB.CloseConnection();
                return dt;
            }
            catch (Exception ex)
            {
                ThrowException("GetVisit", ex);
                return null;
            }
        }
        #endregion

        #region Labtest
        public static DataTable GetAllsymptom()
        {
            try
            {
                Database DB = new Database();
                DB.OpenConnection();
                DataTable dt = DB.GetDataTable("select * from symptom");
                DB.CloseConnection();
                return dt;
            }
            catch (Exception ex)
            {
                ThrowException("GetAllsymptom", ex);
                return null;
            }
        }
        #endregion
    }
}